<?php

namespace app\advertisement\logic;

use app\advertisement\model\Advertising as AdvertisingModel;
use think\facade\Db;
use app\data\util\HttpUtil;
use ba\Exception;
use think\facade\Cache;
use app\common\library\Excel;

class AdvertisingLogic
{
    protected $model = null;

    public function __construct()
    {
        $this->model = new AdvertisingModel();
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $advertiser_id = $params['advertiser_id'] ?? [];
        $limit = $params['limit'] ?? 10;
        $stat_time_day = $params['stat_time_day'] ?? '';
        $admin_id = $params['admin_id'] ?? '';
        $shop_id = $params['shop_id'] ?? '';
        $where = [];
        $where[] = ['a.spend', '>=', 0.001];
        if (!empty($advertiser_id) && $advertiser_id > 0) {
            $where[] = ['a.advertiser_id', '=', (int)$advertiser_id];
        }
        
        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['a.stat_time_day', '>=', $createtimeArr[0]];
            $where[] = ['a.stat_time_day', '<=', $createtimeArr[1]];
        }

        $list = Db::name('advertising_campaign')
            ->field('a.*, b.advertiser_name, COALESCE(c.admin_id, c2.admin_id) as admin_id, COALESCE(c.shop_id, c2.shop_id) as shop_id')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('ba_advaccount_admin c', '
                c.advertiser_id = b.id AND 
                c.createtime <= a.stat_time AND 
                c.updatetime >= a.stat_time AND 
                c.status = "hidden"', 'left')
            ->join('ba_advaccount_admin c2', '
                c2.advertiser_id = b.id AND 
                c2.createtime <= a.stat_time AND 
                c2.status = "normal"', 'left')
            ->where($where)
            ->where(function ($query) use ($admin_id, $shop_id) {
                if (!empty($admin_id) && $admin_id > 0) {  
                    $query->whereRaw("COALESCE(c.admin_id, c2.admin_id) = ?", [(int)$admin_id]);  
                }  
                if (!empty($shop_id) && $shop_id > 0) {  
                    $query->whereRaw("COALESCE(c.shop_id, c2.shop_id) = ?", [(int)$shop_id]);  
                } 
                $query->whereRaw("COALESCE(c.admin_id, c2.admin_id) IS NOT NULL"); 
            })
            ->order('a.stat_time_day desc, a.spend desc')
            ->paginate($limit);
        $lists = $list->items();
        
        foreach ($lists as &$item) {
            $item['admin_name'] = '未分配';
            if(!empty($item['admin_id'])){

                $admin = Cache::store('redis')->get('AdvertisingLogic_admin_name'.$item['admin_id']);
                if(empty($admin)){
                    $admin = Db::name('admin')
                        ->where('id', $item['admin_id'])
                        ->find();
                    Cache::store('redis')->set('AdvertisingLogic_admin_name'.$item['admin_id'], $admin, 300);
                }
                
                $item['admin_name'] = isset($admin['nickname']) ? $admin['nickname'] : '未找到';
            }

            $item['shop_name'] = '未分配';
            if(!empty($item['shop_id'])){

                $shops = Cache::store('redis')->get('AdvertisingLogic_shop_name'.$item['shop_id']);
                if(empty($shops)){
                    $shops = Db::name('shops')
                        ->where('id', $item['shop_id'])
                        ->find();
                    Cache::store('redis')->set('AdvertisingLogic_shop_name'.$item['shop_id'], $shops, 300);
                }
                
                $item['shop_name'] = isset($shops['name']) ? $shops['name'] : '未找到';
            }
        }


        $sum = Db::name('advertising_campaign')
            ->field('sum(a.spend) as spend')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('ba_advaccount_admin c', '
                c.advertiser_id = b.id AND 
                c.createtime <= a.stat_time AND 
                c.updatetime >= a.stat_time AND 
                c.status = "hidden"', 'left')
            ->join('ba_advaccount_admin c2', '
                c2.advertiser_id = b.id AND 
                c2.createtime <= a.stat_time AND 
                c2.status = "normal"', 'left')
            ->where($where)
            ->where(function ($query) use ($admin_id, $shop_id) {
                if (!empty($admin_id) && $admin_id > 0) {  
                    $query->whereRaw("COALESCE(c.admin_id, c2.admin_id) = ?", [(int)$admin_id]);  
                }  
                if (!empty($shop_id) && $shop_id > 0) {  
                    $query->whereRaw("COALESCE(c.shop_id, c2.shop_id) = ?", [(int)$shop_id]);  
                } 
                $query->whereRaw("COALESCE(c.admin_id, c2.admin_id) IS NOT NULL"); 
            })
            ->order('a.stat_time_day desc, a.spend desc')
            ->find();

        $data = [
            'list' => $lists,
            'total' => $list->total(),
            'spend_total' => !empty($sum['spend']) ? number_format($sum['spend'], 2, '.', '') : 0,
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $advertiser_id = $params['advertiser_id'] ?? [];
        $stat_time_day = $params['stat_time_day'] ?? '';
        $admin_id = $params['admin_id'] ?? '';
        $shop_id = $params['shop_id'] ?? '';
        $where = [];
        $where[] = ['a.spend', '>=', 0.001];
        if (!empty($advertiser_id) && $advertiser_id > 0) {
            $where[] = ['a.advertiser_id', '=', (int)$advertiser_id];
        }
        
        if (!empty($stat_time_day)) {
            $createtimeArr = explode(',', $stat_time_day);
            $where[] = ['a.stat_time_day', '>=', $createtimeArr[0]];
            $where[] = ['a.stat_time_day', '<=', $createtimeArr[1]];
        }

        // $where[] = ['a.advertiser_id', '=', 7353109789183967233];

        $lists = Db::name('advertising_campaign')
            ->field('a.*, b.advertiser_name, COALESCE(c.admin_id, c2.admin_id) as admin_id, COALESCE(c.shop_id, c2.shop_id) as shop_id')
            ->alias('a')
            ->join('ba_advaccount b', 'b.advertiser_id = a.advertiser_id', 'left')
            ->join('ba_advaccount_admin c', '
                c.advertiser_id = b.id AND 
                c.createtime <= a.stat_time AND 
                c.updatetime >= a.stat_time AND 
                c.status = "hidden"', 'left')
            ->join('ba_advaccount_admin c2', '
                c2.advertiser_id = b.id AND 
                c2.createtime <= a.stat_time AND 
                c2.status = "normal"', 'left')
            ->where($where)
            ->where(function ($query) use ($admin_id, $shop_id) {
                if (!empty($admin_id) && $admin_id > 0) {  
                    $query->whereRaw("COALESCE(c.admin_id, c2.admin_id) = ?", [(int)$admin_id]);  
                }  
                if (!empty($shop_id) && $shop_id > 0) {  
                    $query->whereRaw("COALESCE(c.shop_id, c2.shop_id) = ?", [(int)$shop_id]);  
                } 
                $query->whereRaw("COALESCE(c.admin_id, c2.admin_id) IS NOT NULL"); 
            })
            ->order('a.stat_time_day desc, a.spend desc')
            ->select()->toarray();
        // dump($lists);die;
        
        foreach ($lists as &$item) {
            $item['admin_name'] = '未分配';
            if(!empty($item['admin_id'])){

                $admin = Cache::store('redis')->get('AdvertisingLogic_admin_name'.$item['admin_id']);
                if(empty($admin)){
                    $admin = Db::name('admin')
                        ->where('id', $item['admin_id'])
                        ->find();
                    Cache::store('redis')->set('AdvertisingLogic_admin_name'.$item['admin_id'], $admin, 300);
                }
                
                $item['admin_name'] = isset($admin['nickname']) ? $admin['nickname'] : '未找到';
            }

            $item['shop_name'] = '未分配';
            if(!empty($item['shop_id'])){

                $shops = Cache::store('redis')->get('AdvertisingLogic_shop_name'.$item['shop_id']);
                if(empty($shops)){
                    $shops = Db::name('shops')
                        ->where('id', $item['shop_id'])
                        ->find();
                    Cache::store('redis')->set('AdvertisingLogic_shop_name'.$item['shop_id'], $shops, 300);
                }
                
                $item['shop_name'] = isset($shops['name']) ? $shops['name'] : '未找到';
            }
        }

        $header[] = ['广告账户', 'advertiser_id'];
        $header[] = ['花费', 'spend'];
        $header[] = ['广告名称', 'advertiser_name'];
        $header[] = ['消耗时间(美国日期)', 'stat_time_day'];
        $header[] = ['归属人', 'admin_name'];
        $header[] = ['归属店铺', 'shop_name'];
        $fileName = "export_advaccount" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @uploadAdv 导入
     * @author zhangheng
     */
    public function uploadAdv($url)
    {
        $data         = Excel::getExcelData('.' . '/' . $url);
        $excelData    = isset($data[0]) ? $data[0] : $data;
        if(!$excelData)   return json(DefaultResponse(201, false, '暂无数据!', null));
        $excelData    = filterArr($excelData);
        $arr_insert   = [];
        foreach ($excelData as $k => $value) {
            //处理时间
            $value['C'] = "2024-01-10";
            $date = date_create_from_format('Y-m-d', $value['C']);
            $time = $date ? $date->format('Y-m-d H:i:s') : null;

            if ($time !== null) {
                $arr_insert = [
                    'advertiser_id' => $value['A'] ?? 0,
                    'spend' => $value['B'] ?? 0,
                    'stat_time_day' => $time,
                    'type' => 1,
                ];
                //只能改手动修改的数据
                $existingAdvertising = Db::name('advertising')
                    ->where('advertiser_id', $arr_insert['advertiser_id'])
                    ->where('stat_time_day', $arr_insert['stat_time_day'])
                    ->where('type', 1)
                    ->find();

                if (empty($existingAdvertising)) {
                    Db::name('advertising')->insert($arr_insert);
                } else {
                    Db::name('advertising')
                        ->where('advertiser_id', $arr_insert['advertiser_id'])
                        ->where('stat_time_day', $arr_insert['stat_time_day'])
                        ->where('type', 1)
                        ->update($arr_insert);
                }
            }
        }
        return  '导入成功';
    }
}
